package com.quxian.chambercommerce.util;

import jxl.*;
import org.apache.log4j.Logger;

import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * a util help to read the excel file
 */
public class ExcelImportUtil {
    private static final Logger logger = Logger.getLogger(ExcelImportUtil.class);
    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    protected ExcelImportUtil() {
    }

    ;

    /**
     * Parse the excel file;
     *
     * @param in         excel file
     * @param clazz      target object type
     * @param fields     the excel's field order
     * @param sheetIndex sheet index, start begin 0, default 0
     * @param offset     start begin 0, default 0	(it means left cell offset)
     * @param startRow   start begin 0, default 1
     * @return
     */
    public static <T> List<T> readExcel(InputStream in, Class<T> clazz, String[] fields, int sheetIndex, int offset, int startRow) {
        if (in == null || clazz == null || fields == null || fields.length <= 0 || offset < 0 || startRow < 0) {
            logger.error("excel read error : the param is null...");
            return null;
        }

        List<T> list = new ArrayList<T>();

        logger.info("Excel import begin...");
        logger.info("Class : " + clazz.getName());
        logger.info("offset : " + offset);
        logger.info("fields : ");
        for (int i = 0; i < fields.length; i++) {
            logger.info(i + "\t: " + fields[i]);
        }

        Method[] methods = getMethod(clazz, fields);

        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(in);
            Sheet sheet = workbook.getSheet(sheetIndex);
            for (int i = 0; i < sheet.getRows(); i++) {
                if (i < startRow) {
                    continue;
                }
                T inst = clazz.newInstance();
                for (int j = 0; j < fields.length; j++) {
                    int col = offset + j;
                    Cell cell = sheet.getCell(col, i);
                    if (cell == null || cell.getContents() == null || isEmpty(cell.getContents())) {
                        continue;
                    }
                    String cellVal = cell.getContents();
                    Object value = cellVal;
                    Class<?> type = methods[j].getParameterTypes()[0];
                    try {
                        if (Integer.class == type || int.class == type) {
                            value = Integer.parseInt(cellVal);
                        } else if (Date.class == type) {
                            if (cell.getType() == CellType.DATE) {
                                DateCell dc = (DateCell) cell;
                                Date datec2 = dc.getDate();
                                long Time = (datec2.getTime() / 1000) - 60 * 60 * 8;
                                datec2.setTime(Time * 1000);
                                value = datec2;
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    methods[j].invoke(inst, value);
                }
//                 String[] par = new String[1];
//                par[0] ="excelIndex";
//                Method[] method = getMethod(clazz, par);
//                if(null !=method[0]){
//                    Method methodExcelIndex = method[0];
//                    methodExcelIndex.invoke(inst,i);
//
//                }
                list.add(inst);
            }
        } catch (Exception e) {
            e.printStackTrace();
//            throw new RuntimeException(e);
        }

//		return null;
        return list;
    }


    /**
     * Parse the excel file;
     *
     * @param in         excel file
     * @param fields     the excel's field order
     * @param sheetIndex sheet index, start begin 0, default 0
     * @param offset     start begin 0, default 0	(it means left cell offset)
     * @param startRow   start begin 0, default 1
     * @return
     */
    public static List<Map<String, Object>> readExcel(InputStream in, String[] fields, int sheetIndex, int offset, int startRow){
        if (in == null || fields == null || fields.length <= 0 || offset < 0 || startRow < 0) {
            logger.error("excel read error : the param is null...");
            return null;
        }

        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        logger.info("Excel import begin...");
        logger.info("offset : " + offset);
        logger.info("fields : ");
        for (int i = 0; i < fields.length; i++) {
            logger.info(i + "\t: " + fields[i]);
        }


        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(in);
            Sheet sheet = workbook.getSheet(sheetIndex);
            for (int i = 0; i < sheet.getRows(); i++) {
                if (i < startRow) {
                    continue;
                }
                Map<String, Object> inst = new LinkedHashMap<String, Object>();
                for (int j = 0; j < fields.length; j++) {
                    int col = offset + j;
                    Cell cell = sheet.getCell(col, i);
                    if (cell == null || cell.getContents() == null || isEmpty(cell.getContents())) {
                        continue;
                    }
                    String cellVal = cell.getContents();
                    Object value = cellVal;
                    try {
                        if (cell.getType() == CellType.DATE) {

                            DateCell dc = (DateCell) cell;
                            Date datec2 = dc.getDate();
                            long Time = (datec2.getTime() / 1000) - 60 * 60 * 8;
                            datec2.setTime(Time * 1000);
                            value = datec2;
                        }

                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    inst.put(fields[j], value);
                }
                list.add(inst);
            }
        } catch (Exception e) {
            logger.error("Import excel error...", e);
//            throw new RuntimeException(e);
        }

        return list;
    }


    public static <T> List<T> readExcel(InputStream in, Class<T> clazz, String[] fields) {
        return readExcel(in, clazz, fields, 0, 1, 1);
    }

    /**
     * get the method mapping for the field
     *
     * @param <T>
     * @param clazz
     * @param fields
     * @return
     */
    private static <T> Method[] getMethod(Class<T> clazz, String[] fields) {
        Method[] rtnMethods = new Method[fields.length];
        Method[] allMethods = clazz.getDeclaredMethods();
        for (int i = 0; i < fields.length; i++) {
            String field = fields[i];
            for (Method method : allMethods) {
                Class<?>[] types = method.getParameterTypes();
                if (types == null || types.length != 1) {
                    continue;
                }
                String methodName = "set" + field.substring(0, 1).toUpperCase() + field.substring(1);
                if (methodName.equals(method.getName())) {
                    rtnMethods[i] = method;
                    break;
                }
            }
        }
        return rtnMethods;
    }

    public static boolean isEmpty(String str) {
        if (str == null || str.trim().length() <= 0 || "null".equals(str)) {
            return true;
        }
        return false;
    }

}